package org.bzjj.servlet.bean.ctrl;

import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.log4j.Logger;
import org.bzjj.log.MyLog;
import org.bzjj.servlet.bean.CarSbdh;
import org.bzjj.sql.OracleCon;
import org.bzjj.tool.ChatMethod;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

public class GetCarSbdh extends HttpServlet {

	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;

	public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		ArrayList<CarSbdh> cars = getCarList(
				"select case when instr(upper(t.clsbdh),'O')>0 then instr(upper(t.clsbdh),'O') when instr(upper(t.clsbdh),'I')>0 then instr(upper(t.clsbdh),'I') when instr(upper(t.clsbdh),'Q')>0 then instr(upper(t.clsbdh),'Q') else 0 end errpos,length(t.clsbdh) dhlen,t.hpzl,t.hphm,t.clsbdh,t.gxsj from veh_is_flow t where t.jylb!='01' and gxsj>sysdate-15 and (instr(t.clsbdh,'O')>0 or instr(t.clsbdh,'o')>0 or instr(t.clsbdh,'i')>0 or instr(t.clsbdh,'I')>0 or instr(t.clsbdh,'q')>0 or instr(t.clsbdh,'Q')>0 or length(t.clsbdh)!='17') order by gxsj desc");
		JSONArray array = JSONArray.fromObject(cars);

		JSONObject object = new JSONObject();
		object.element("total", cars.size());// 表中所有记录条数
		object.element("rows", array);

		ChatMethod.setResponse(response, object.toString());
	}

	/**
	 * 从数据库查询所有用户
	 * 
	 * @param query
	 *            查询语句
	 * @return
	 */
	public static ArrayList<CarSbdh> getCarList(String query) {
		ArrayList<CarSbdh> rs = new ArrayList<CarSbdh>();
		ResultSet result = null;
		CarSbdh car = null;
		Connection connection = OracleCon.getDBonnect().getConnection();

		if (connection == null) {
			Logger logger = MyLog.getLog("GetCarSbdh");
			logger.error("连接建立失败");
			return rs;
		}

		Statement stmt = null;
		try {
			stmt = connection.createStatement();
			result = stmt.executeQuery(query);

			while (result.next()) {
				car = new CarSbdh(result.getString("errpos"), result.getString("dhlen"), result.getString("hpzl"),
						result.getString("hphm"), result.getString("clsbdh"), result.getString("gxsj"));
				rs.add(car);
			}

		} catch (SQLException e) {
			Logger logger = MyLog.getLog("GetCarSbdh");
			logger.error(e.getMessage());
		}

		OracleCon.getDBonnect().closeConnection(result, stmt, connection);

		return rs;

	}

}
